﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace BasicAdoNet
{
    public class TerritoriesRepository : ITerritoriesRepository
    {
        private readonly SqlConnection _connection;

        public TerritoriesRepository()
        {
            _connection = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ConnectionString);
        }

        public IEnumerable<Territory> GetTerritoriesByRegionId(int id)
        {
            var territories = new List<Territory>();

            var command = new SqlCommand("GetTerritoriesByRegionId", _connection);
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.AddWithValue("@RegionId", id);

            _connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                var territory = new Territory();

                territory.Id = reader["TerritoryId"] as string;
                territory.Description = reader["TerritoryDescription"] as string;

                territory.Region = new Region(this);

                territory.Region.Id = Convert.ToInt32(reader["RegionId"]);
                territory.Region.Description = reader["RegionDescription"] as string;

                territories.Add(territory);
            }

            _connection.Close();

            return territories;
        }

        public IEnumerable<Territory> GetTerritoriesFilterByDescription(string description)
        {
            var territories = new List<Territory>();

            var command = new SqlCommand("GetTerritoriesFilterByDescription", _connection);
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.AddWithValue("@TerritoryDescription", description);


            _connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                var territory = new Territory();

                territory.Id = reader["TerritoryId"] as string;
                territory.Description = reader["TerritoryDescription"] as string;

                territory.Region = new Region(this);

                territory.Region.Id = Convert.ToInt32(reader["RegionId"]);
                territory.Region.Description = reader["RegionDescription"] as string;

                territories.Add(territory);
            }

            _connection.Close();

            return territories;
        }


        public IEnumerable<Region> GetAllRegions()
        {
            var regions = new List<Region>();

            var command = new SqlCommand("SELECT RegionId, RegionDescription FROM Region", _connection);

            _connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                var region = new Region(this);

                region.Id = Convert.ToInt32(reader["RegionId"]);
                region.Description = reader["RegionDescription"] as string;

                regions.Add(region);
            }

            _connection.Close();

            return regions;
        }
    }
}